﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class Receive_BLL : IProject
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable GetDataProjectByYear(int Year, string HeaderID)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);   // prj
                dbManager.AddParameters(1, "@Year", Year);
                dbManager.AddParameters(2, "@HeaderID", HeaderID);
                
                string sql = "select r.bgm_rec_seq as Sequence, r.bgm_rec_code as bgm_prj_code"
                           + ", r.bgm_rec_name as bgm_prj_name, s.bgm_strat_list_name as Strategy, r.bgm_strat_list_seq"
                           + ", s.bgm_strat_list_name , j.job_site_name"
                           + ", coalesce(r.total_amount, 0) as total_amount, coalesce(r.approve_amount, 0) as approve_amount"
                           + ", h.bgm_rec_header_seq as bgm_proj_header_seq, h.bgm_rec_header_name as bgm_proj_header_name"
                           + " from bgm_receive_header h"
                               + " left join bgm_receive r on h.bgm_rec_header_seq = r.bgm_rec_header_seq"
                               + " left join bgm_strategy_data_list s on r.bgm_strat_list_seq = s.bgm_strat_list_seq"
                               + " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
                           + " where r.record_status not like @RecordStatus"
                           + " and r.bgm_year = @Year"
                           + ((HeaderID.Length > 0) ? " and h.bgm_rec_header_seq = @HeaderID" : "")
                           + " order by r.bgm_rec_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }        
        }

        public DataTable ListProjectExpenseResource(string bgm_prj_seq, string bgm_exp_seq, string bgm_source_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataTable ListProjectExpenseGrid(string rec_seq, string source, string exp_grp, bool show_all_exp)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@seq", rec_seq);
                dbManager.AddParameters(2, "@source", source);
                dbManager.AddParameters(3, "@expgrp", exp_grp);

                string sql = " select e.bgm_exp_seq as value, e.bgm_exp_name as text"
                            + ", e1.bgm_exp_seq as bgm_exp_seq_1, e1.bgm_exp_name as bgm_exp_name_1"
                            + ", e2.bgm_exp_seq as bgm_exp_seq_2, e2.bgm_exp_name as bgm_exp_name_2"
                            + ", e3.bgm_exp_seq as bgm_exp_seq_3, e3.bgm_exp_name as bgm_exp_name_3"
                            + ", eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name, eg.bgm_exp_grp_x"
                            + ", sum(re.total_amount) as total_amount, re.bgm_rec_exp_seq as bgm_proj_exp_seq"
                            + ", re.icm_res_item_seq, re.unit_price, re.unit_qty"
                        + " from bgm_mas_expense e"
                            + " left join bgm_mas_expense e1 on e1.bgm_exp_seq = e.upper_bgm_exp_seq"
                            + " left join bgm_mas_expense e2 on e2.bgm_exp_seq = e1.upper_bgm_exp_seq"
                            + " left join bgm_mas_expense e3 on e3.bgm_exp_seq = e2.upper_bgm_exp_seq"
                            + " left join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                            + " left join bgm_receive_expense re on re.bgm_exp_seq = e.bgm_exp_seq and re.bgm_rec_seq = @seq and re.bgm_source_seq = @source"

                            + " inner join bgm_mas_expense_source es on e.bgm_exp_seq = es.bgm_exp_seq"
                            + (source.Length > 0 ? " and es.bgm_source_seq = @source" : "")

                        + " where e.record_status = @RecordStatus"
                            + ((show_all_exp) ? "" : " and re.bgm_rec_seq = @seq")
                            + ((exp_grp.Length > 0) ? " and e.bgm_exp_grp_seq = @expgrp" : "")
                            + " and e.bgm_exp_seq not in ( select upper_bgm_exp_seq from bgm_mas_expense where record_status = @RecordStatus and upper_bgm_exp_seq is not null)"
                        + " group by e.bgm_exp_seq, e.bgm_exp_name, eg.bgm_exp_grp_seq, eg.bgm_exp_grp_code, eg.bgm_exp_grp_name"
                            + ", e1.bgm_exp_seq, e1.bgm_exp_name, e2.bgm_exp_seq, e2.bgm_exp_name, e3.bgm_exp_seq, e3.bgm_exp_name"
                        + " order by eg.bgm_exp_grp_code, e.bgm_exp_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListProjectOptionGrid(string app_seq, string optionType)
        {
            try
            {
                DataTable dtb = GetDataProjectBySequence(app_seq);

                string strYear = dtb.Rows[0]["bgm_year"].ToString();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@seq", app_seq);
                dbManager.AddParameters(2, "@type", optionType);

                string sql = " select o.bgm_option_seq as value, o.bgm_option_name as text"
                            + ", o1.bgm_option_seq as bgm_option_seq_1, o1.bgm_option_name as bgm_option_name_1"
                            + ", re.weight, re.bgm_req_opt_seq as bgm_proj_opt_seq"
                        + " from bgm_mas_option o"
                            + " left join bgm_mas_option o1 on o1.bgm_option_seq = o.upper_bgm_option_seq"
                            + " left join bgm_approval_option re on re.bgm_option_seq = o.bgm_option_seq and re.bgm_app_seq = @seq"
                        + " where o.bgm_option_type_seq = @type"
                            + " and o.bgm_option_seq not in ( select upper_bgm_option_seq from bgm_mas_option where record_status = @RecordStatus and upper_bgm_option_seq is not null)"
                            + (optionType == "3" ? " and o.bgm_year = " + strYear : "")
                        + " order by o.bgm_option_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListProjectOptionRepeater(string req_seq, string upper_bgm_option_seq, string optionType)
        {
            try
            {
                DataTable dtb = GetDataProjectBySequence(req_seq);

                string strYear = dtb.Rows[0]["bgm_year"].ToString();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@seq", req_seq);
                dbManager.AddParameters(2, "@type", optionType);
                dbManager.AddParameters(3, "@upper_bgm_option_seq", upper_bgm_option_seq);

                string sql = "select o.bgm_option_seq, o.bgm_option_name"
                    + ", o.upper_bgm_option_seq, coalesce(o1.child_count, 0) as child_count"
                    + ", re.weight, re.bgm_req_opt_seq"
                    + " from bgm_mas_option o"
                    + " left join ( select upper_bgm_option_seq, count( * ) as child_count"
                        + " from bgm_mas_option"
                        + " where record_status not like @RecordStatus"
                        + " group by upper_bgm_option_seq"
                        + ") o1 on o.bgm_option_seq = o1.upper_bgm_option_seq"
                    + " left join bgm_request_option re on re.bgm_option_seq = o.bgm_option_seq"
                        + " and re.bgm_req_seq = @seq"
                    + " where o.record_status not like @RecordStatus"
                    + (optionType != null ? " and o.bgm_option_type_seq = @type" : "")
                    + (upper_bgm_option_seq != null ? " and o.upper_bgm_option_seq = @upper_bgm_option_seq" : " and o.upper_bgm_option_seq is null")
                    + " order by o.bgm_option_code";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListProjectOptionTreeView(string req_seq, string optionType)
        {
            try
            {
                //DataTable dtb = GetDataProjectBySequence(req_seq);

                //string strYear = dtb.Rows[0]["bgm_year"].ToString();

                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@seq", req_seq);
                dbManager.AddParameters(2, "@type", optionType);
                //dbManager.AddParameters(3, "@bgm_year", strYear);

                string sql = "select o.bgm_option_seq, o.bgm_option_name, o.upper_bgm_option_seq, re.bgm_rec_opt_seq as bgm_proj_opt_seq"
                    + " from bgm_mas_option o"
                    //+ " left join ( select upper_bgm_option_seq, count( * ) as child_count"
                    //    + " from bgm_mas_option"
                    //    + " where record_status not like @RecordStatus"
                    //    + " group by upper_bgm_option_seq"
                    //    + ") o1 on o.bgm_option_seq = o1.upper_bgm_option_seq"
                    + " left join bgm_receive_option re on re.bgm_option_seq = o.bgm_option_seq"
                        + " and re.bgm_rec_seq = @seq"
                    + " where o.record_status not like @RecordStatus"
                    + (optionType != null ? " and o.bgm_option_type_seq = @type" : "")
                    //+ (optionType == "3" ? " and o.bgm_year = @bgm_year" : "")
                    + " order by o.bgm_option_code";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectBySequence(string seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@seq", seq);

                string sql = "select  r.bgm_rec_seq as Sequence"
                           + ", r.bgm_rec_code as bgm_prj_code, r.bgm_rec_name as bgm_prj_name"
                           + ", r.bgm_rec_descr as bgm_prj_descr, r.bgm_rec_scope as bgm_prj_scope"
                           + ", r.bgm_rec_date as bgm_prj_date, r.bgm_rec_reason as bgm_prj_reason, r.bgm_rec_object as bgm_prj_object"
                           + ", r.bgm_rec_target as bgm_prj_target, r.bgm_rec_plan as bgm_prj_plan, r.bgm_rec_aim as bgm_prj_aim, r.bgm_rec_profit as bgm_prj_profit, r.bgm_rec_remark as bgm_prj_remark"
                           + ", s.bgm_strat_list_name , j.job_site_name"
                           + ", r.bgm_year, r.bgm_rec_date as bgm_prj_date, r.bgm_strat_list_seq, r.job_site_seq, s.bgm_strat_list_name, j.job_site_name, ms.bgm_scope_name"
                           + ", r.prj_type_seq, pt.prj_type_name"
                           + " from bgm_receive r"
                               + " left join bgm_strategy_data_list s on r.bgm_strat_list_seq = s.bgm_strat_list_seq"
                               + " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
                               + " left join bgm_mas_scopes ms on r.bgm_rec_scope = ms.bgm_scope_seq"
                               + " left join bgm_mas_project_type pt on pt.prj_type_seq = r.prj_type_seq"
                           + " where r.bgm_rec_seq = @seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectBySequenceForGridIn(string seq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                string sql = "select s.bgm_source_seq, s.bgm_source_name"
                        + ", coalesce(sum(ae.approve_amount), 0) as approve_amount"
                        + ", coalesce(sum(ae.total_amount), 0) as total_amount"
                           + " from  bgm_mas_source s "
                               + " left join bgm_receive_expense ae on ae.bgm_source_seq = s.bgm_source_seq and ae.bgm_rec_seq = @seq"
                               + " left join bgm_receive r on ae.bgm_rec_seq = r.bgm_rec_seq"
                           + " where s.record_status = @RecordStatus"
                               + " and s.bgm_source_seq not in (select upper_bgm_source_seq from bgm_mas_source where record_status = @RecordStatus and upper_bgm_source_seq is not null)"
                           + " group by s.bgm_source_seq, s.bgm_source_name"
                           + " order by s.bgm_source_code";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpGrpBySequenceForGridIn(string seq)
        {
            return null;
        }

        public DataTable GetDataProjectAndSource(string HeaderID, string StrategyID)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@HeaderID", HeaderID);
                dbManager.AddParameters(2, "@StrategyID", StrategyID);

                string sql = " select  ast.bgm_app_strat_seq as Sequence"
                            + ", s.bgm_source_name"
                            + ", ast.total_amount"
                            + ", ast.approve_amount"
                            + " from bgm_approval_strategy ast "
                            + " inner join bgm_mas_source s on s.bgm_source_seq = ast.bgm_source_seq"
                            + " and ast.bgm_app_header_seq=@HeaderID and ast.bgm_strat_list_seq=@StrategyID";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataTable GetDataProjectAndSourceGroup(string HeaderID, string StrategyID)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@HeaderID", HeaderID);
                dbManager.AddParameters(2, "@StrategyID", StrategyID);

                string sql = " select  aps.bgm_app_source_seq as Sequence"
                            + ", a.bgm_app_seq "
                            + ", a.bgm_app_name as bgm_prj_name"
                            + ", s.bgm_source_name"
                            + ", aps.total_amount"
                            + ", aps.approve_amount"
                            + " from bgm_approval_source aps "
                            + " inner join bgm_approval a on aps.bgm_app_seq = a.bgm_app_seq "
                                + "and a.bgm_app_header_seq = @HeaderID and a.bgm_strat_list_seq = @StrategyID"
                            + " inner join bgm_mas_source s on s.bgm_source_seq = aps.bgm_source_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectOption(string bgm_rec_seq, string bgm_option_type_seq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@seq", bgm_rec_seq);
                dbManager.AddParameters(1, "@type", bgm_option_type_seq);

                string sql = "select ro.bgm_rec_opt_seq as Sequence, o.bgm_option_name, ro.weight, ro.bgm_option_seq"
                           + ", (weight * (select coalesce(sum(total_amount), 0) from bgm_receive_expense s where s.bgm_rec_seq = ro.bgm_rec_seq )) / 100 as total_amount, descr"
                           + " from bgm_receive_option ro"
                           + " inner join bgm_mas_option o on ro.bgm_option_seq = o.bgm_option_seq"
                           + " where ro.bgm_rec_seq = @seq"
                           + " and o.bgm_option_type_seq = @type";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectOptionBySequence(string seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select ro.bgm_rec_opt_seq as Sequence, o.bgm_option_name, o.bgm_option_seq, ro.weight"
                           + ", (weight * (select coalesce(sum(total_amount), 0) from bgm_receive_expense s where s.bgm_rec_seq = ro.bgm_rec_seq )) / 100 as total_amount, descr"
                           + " from bgm_receive_option ro"
                           + " inner join bgm_mas_option o on ro.bgm_option_seq = o.bgm_option_seq"
                           + " where ro.bgm_rec_opt_seq =" + seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectKPI(string bgm_rec_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select rk.bgm_rec_kpi_seq as Sequence, rk.bgm_rec_kpi_name as bgm_prj_kpi_name, rk.unit, rk.bgm_rec_kpi_weight as bgm_prj_kpi_weight"
                        + ", rk.target_m1, rk.target_m2, rk.target_m3, rk.target_m4"
                        + ", rk.target_m5, rk.target_m6, rk.target_m7, rk.target_m8"
                        + ", rk.target_m9, rk.target_m10, rk.target_m11, rk.target_m12"
                    + ", kt.bgm_kpi_type_name"
                           + " from bgm_receive_kpi rk"
                           + " inner join bgm_mas_kpi_type kt on rk.bgm_kpi_type_seq = kt.bgm_kpi_type_seq"
                           + " where rk.bgm_rec_seq =" + bgm_rec_seq;

                //SELECT * FROM branch INNER JOIN member ON ( branch.branch_id = member.branch_id)

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectKPIBySequence(string bgm_rec_kpi_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select *, bgm_rec_kpi_name as bgm_prj_kpi_name, bgm_rec_kpi_descr as bgm_prj_kpi_descr, bgm_rec_kpi_weight as bgm_prj_kpi_weight"
                           + " from bgm_receive_kpi"
                           + " where bgm_rec_kpi_seq =" + bgm_rec_kpi_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectActivityPlan(string bgm_rec_seq)
        {
            try
            {
                string sql = "select bgm_rec_act_plan_seq as Sequence, descr"
                       + " , act_month1, act_month2, act_month3, act_month4"
                       + " , act_month5, act_month6, act_month7, act_month8"
                       + " , act_month9, act_month10, act_month11, act_month12"
                       + " from bgm_receive_activity_plan"
                       + " where bgm_rec_seq =" + bgm_rec_seq;
  
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectActivityPlanBySequence(string bgm_rec_act_plan_seq)
        {
            try
            {
                //string sql = "select ap.bgm_rec_act_plan_seq as Sequence, ap.descr"
                //       + " , ap.act_month1, ap.act_month2, ap.act_month3, ap.act_month4"
                //       + " , ap.act_month5, ap.act_month6, ap.act_month7, ap.act_month8"
                //       + " , ap.act_month9, ap.act_month10, ap.act_month11, ap.act_month12"
                //       + " , apt.bgm_rec_act_plan_target_seq, apt.target, apt.start_month, apt.end_month"
                //       + " from bgm_receive_activitiy_plan ap left join bgm_receive_activity_plan_target apt on ap.bgm_rec_act_plan_seq = apt.bgm_rec_act_plan_seq"
                //       + " where apt.bgm_rec_act_plan_seq =" + bgm_rec_act_plan_seq;

                string sql = "select ap.bgm_rec_act_plan_seq as Sequence, ap.descr"
                       + " , ap.act_month1, ap.act_month2, ap.act_month3, ap.act_month4"
                       + " , ap.act_month5, ap.act_month6, ap.act_month7, ap.act_month8"
                       + " , ap.act_month9, ap.act_month10, ap.act_month11, ap.act_month12"
                       + " , (select count(*) from bgm_receive_activity_plan_target as apt where apt.bgm_rec_act_plan_seq =" + bgm_rec_act_plan_seq + ") as rowTarget"
                       + " from bgm_receive_activity_plan ap"
                       + " where ap.bgm_rec_act_plan_seq =" + bgm_rec_act_plan_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectActivityPlanTarget(string bgm_rec_act_plan_seq)
        {
            try
            {
                string sql = "select bgm_rec_act_plan_target_seq as Sequence, target, start_month, end_month"
                       + " from bgm_receive_activity_plan_target"
                       + " where bgm_rec_act_plan_seq =" + bgm_rec_act_plan_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpense(string bgm_rec_seq, string source, string bgm_exp_grp_seq)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@bgm_rec_seq", bgm_rec_seq);
                dbManager.AddParameters(1, "@source", source);
                dbManager.AddParameters(2, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                string sql = "select eg.bgm_exp_grp_name, e1.bgm_exp_name as name, r.bgm_rec_ctrl_seq, re.bgm_rec_ctrl_exp_seq as Sequence, re.bgm_source_seq, s.bgm_source_name, eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name, e1.bgm_exp_seq, e.bgm_exp_name, re.bgm_exp_seq, e.bgm_exp_name, re.icm_res_item_seq, i.icm_res_item_name, re.total_amount"
                    + " from bgm_receive_control r"
                    + " inner join bgm_receive_control_expense re on re.bgm_rec_ctrl_seq = r.bgm_rec_ctrl_seq"
                    + " left join bgm_mas_source s on re.bgm_source_seq = s.bgm_source_seq"
                    + " left join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                    + " left join bgm_mas_expense e1 on e1.bgm_exp_seq = e.upper_bgm_exp_seq and e1.upper_bgm_exp_seq is null"
                    + " left join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                    + " left join icm_mas_resource_item i on i.icm_res_item_seq = re.icm_res_item_seq"
                    + " where r.bgm_rec_seq = @bgm_rec_seq"
                    + ((source.Length > 0) ? " and re.bgm_source_seq = " + source : "")
                    + ((bgm_exp_grp_seq.Length > 0) ? " and e.bgm_exp_grp_seq = " + bgm_exp_grp_seq : "");

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectExpenseOnlyExp(string bgm_rec_seq, string source, string bgm_exp_grp_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select r.bgm_rec_seq, re.bgm_rec_exp_seq, re.bgm_exp_seq"
                    + ", e.bgm_exp_name, re.total_amount"
                    + " from bgm_receive r"
                    + " inner join bgm_receive_expense re on re.bgm_rec_seq = r.bgm_rec_seq"
                    + " left join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                    + " where r.bgm_rec_seq =" + bgm_rec_seq
                    + ((source.Length > 0) ? " and re.bgm_source_seq = " + source : "")
                    + ((bgm_exp_grp_seq.Length > 0) ? " and e.bgm_exp_grp_seq = " + bgm_exp_grp_seq : "");

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectReportExpense(string bgm_req_seq, string source)
        {
            return null;
        }

        public DataTable GetDataProjectExpenseBySequence(string bgm_rec_exp_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select s.bgm_source_name, eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name, e1.bgm_exp_name, e.bgm_exp_name, i.icm_res_item_name, re.*"
                           + " from bgm_receive_expense re"
                           + " left join bgm_mas_source s on re.bgm_source_seq = s.bgm_source_seq"
                           + " left join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                           + " left join bgm_mas_expense e1 on e1.bgm_exp_seq = e.upper_bgm_exp_seq"
                           + " left join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                           + " left join icm_mas_resource_item i on i.icm_res_item_seq = re.icm_res_item_seq"

                           + " where re.bgm_rec_exp_seq =" + bgm_rec_exp_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public DataTable GetDataProjectExpenseGroup(string bgm_rec_seq, string bgm_source_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select eg.bgm_exp_grp_name, coalesce(sum(re.total_amount), 0) as total_amount"
                           + " from bgm_mas_expense_group eg"
                           + " left join bgm_mas_source s on eg.bgm_source_seq = s.bgm_source_seq"
                           + " left join bgm_mas_expense e on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                           + " left join bgm_receive_expense re on re.bgm_exp_seq = e.bgm_exp_seq"
                           + " where eg.record_status = 'U'"
                           + " and re.bgm_rec_seq =" + bgm_rec_seq
                           + " and re.bgm_source_seq =" + bgm_source_seq
                           + " group by eg.bgm_exp_grp_name, eg.bgm_exp_grp_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public DataTable GetDataProjectExpensePlan(string bgm_rec_seq, string bgm_source_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select rp.bgm_rec_exp_plan_seq as Sequence, e.bgm_exp_name, re.bgm_rec_exp_seq as bgm_prj_exp_seq, re.total_amount" 
                           + " , rp.amount_month1, rp.amount_month2, rp.amount_month3, rp.amount_month4"
                           + " , rp.amount_month5, rp.amount_month6, rp.amount_month7, rp.amount_month8"
                           + " , rp.amount_month9, rp.amount_month10, rp.amount_month11, rp.amount_month12"
                           + " from bgm_receive r"
                           + " inner join bgm_receive_expense re on r.bgm_rec_seq = re.bgm_rec_seq"
                           + " left join bgm_receive_expense_plan rp on rp.bgm_rec_exp_seq = re.bgm_rec_exp_seq"
                           + " inner join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                           + " where r.bgm_rec_seq =" + bgm_rec_seq
                           + (bgm_source_seq.Length > 0 ? " and re.bgm_source_seq =" + bgm_source_seq : "");

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public DataTable GetDataProjectExpensePlanBySequence(string bgm_rec_exp_plan_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select *"
                           + " from bgm_receive_expense_plan"
                           + " where bgm_rec_exp_plan_seq = " + bgm_rec_exp_plan_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public int InsertProject(string header_seq, string bgm_year, DateTime bgm_app_date
            , string bgm_strat_list_seq, string job_site_seq, string prj_type_seq, string bgm_app_manager
            , string bgm_app_scope, string bgm_app_code, string bgm_app_name, string bgm_app_descr
            , string bgm_app_reason, string bgm_app_object, string bgm_app_plan, string bgm_app_target
            , string bgm_app_remark, string CreatedBy)
        {
            // ไม่แน่ใจ
            return InsertProject(header_seq, bgm_year, bgm_app_date
                          , bgm_strat_list_seq, job_site_seq, prj_type_seq, bgm_app_manager
                          , bgm_app_scope, bgm_app_code, bgm_app_name, bgm_app_descr
                          , bgm_app_reason, bgm_app_object, bgm_app_plan, bgm_app_target
                          , bgm_app_remark, CreatedBy, "");

        }

        public int InsertProject(string bgm_year, DateTime bgm_app_date, string bgm_app_name
            , string bgm_strat_list_seq, string job_site_seq, string prj_type_seq, string bgm_app_scope
            , string bgm_app_reason, string bgm_app_object, string bgm_app_plan, string bgm_app_target
            , string bgm_req_aim, string bgm_req_profit
            , string bgm_app_remark, string date_start, string date_end, string CreatedBy)
        {
            // ไม่แน่ใจ
            return InsertProject(bgm_year, bgm_app_date, bgm_app_name
                                 , bgm_strat_list_seq, job_site_seq, prj_type_seq, bgm_app_scope
                                 , bgm_app_reason, bgm_app_object, bgm_app_plan, bgm_app_target
                                 , bgm_req_aim, bgm_req_profit
                                 , bgm_app_remark, date_start, date_end, CreatedBy, "");
        }
        public void UpdateProject(string bgm_proj_seq, DateTime bgm_proj_date, string bgm_proj_code
            , string bgm_proj_name, string bgm_proj_descr, string job_site_seq_main, string bgm_proj_manager, string bgm_proj_scope
            , string bgm_proj_reason, string bgm_proj_object, string bgm_proj_target, string bgm_proj_plan, string bgm_proj_aim, string bgm_proj_profit
            , string bgm_proj_remark, string bgm_strat_list_seq, string prj_type_seq, DateTime date_start, DateTime date_end
            , string gfmis_code, string UpdatedBy)
        {
            //ไม่แน่ใจ
            
        }
        public int InsertProject(string bgm_year, DateTime bgm_app_date, string bgm_app_name
            , string bgm_strat_list_seq, string job_site_seq, string prj_type_seq, string bgm_app_scope
            , string bgm_app_reason, string bgm_app_object, string bgm_app_plan, string bgm_app_target
            , string bgm_req_aim, string bgm_req_profit
            , string bgm_app_remark, string date_start, string date_end, string CreatedBy, string job_site_seq_main)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@bgm_year", bgm_year);
                dbManager.AddParameters(1, "@bgm_app_date", bgm_app_date);
                dbManager.AddParameters(2, "@bgm_app_name", bgm_app_name);
                dbManager.AddParameters(3, "@bgm_strat_list_seq", bgm_strat_list_seq);
                dbManager.AddParameters(4, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(5, "@prj_type_seq", prj_type_seq);
                dbManager.AddParameters(6, "@bgm_app_scope", bgm_app_scope);
                dbManager.AddParameters(7, "@bgm_app_reason", bgm_app_reason);
                dbManager.AddParameters(8, "@bgm_app_object", bgm_app_object);
                dbManager.AddParameters(9, "@bgm_app_plan", bgm_app_plan);
                dbManager.AddParameters(10, "@bgm_app_target", bgm_app_target);
                dbManager.AddParameters(11, "@bgm_app_remark", bgm_app_remark);
                dbManager.AddParameters(12, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(13, "@created_by", CreatedBy);
                dbManager.AddParameters(14, "@created_when", DateTime.Now);
                dbManager.AddParameters(15, "@job_site_seq_main", job_site_seq_main);

                string strsql = "Insert into bgm_approval (bgm_year, bgm_app_date, bgm_app_name, bgm_strat_list_seq, job_site_seq"
                              + ", prj_type_seq, bgm_app_scope, bgm_app_reason, bgm_app_object, bgm_req_plan, bgm_req_target"
                              + ", bgm_req_remark, record_status, created_by, created_when, job_site_seq_main)"
                              + " values(@bgm_year, @bgm_req_date, @bgm_req_name, @bgm_strat_list_seq, @job_site_seq"
                              + ", @prj_type_seq, @bgm_req_scope, @bgm_req_reason, @bgm_req_object, @bgm_req_plan, @bgm_req_target"
                              + ", @bgm_req_remark, @record_status, @created_by, @created_when, @job_site_seq_main)";

                dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                string strsqlLast_insert = "select distinct scope_identity() FROM bgm_approval";
                DataSet ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strsqlLast_insert);
                dbManager.CommitTransaction();
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            catch
            {
                dbManager.RollBackTransaction();
                return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public int InsertProject(string bgm_rec_header_seq, string bgm_year, DateTime bgm_rec_date
            , string bgm_strat_list_seq, string job_site_seq, string prj_type_seq, string bgm_rec_manager
            , string bgm_rec_scope, string bgm_rec_code, string bgm_rec_name, string bgm_rec_descr
            , string bgm_rec_reason, string bgm_rec_object, string bgm_rec_plan, string bgm_rec_target
            , string bgm_rec_remark, string CreatedBy, string job_site_seq_main)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(20);
                dbManager.AddParameters(0, "@bgm_rec_header_seq", bgm_rec_header_seq);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);
                dbManager.AddParameters(2, "@bgm_rec_date", bgm_rec_date); //bgm_rec_date
                dbManager.AddParameters(3, "@bgm_strat_list_seq", bgm_strat_list_seq);
                dbManager.AddParameters(4, "@job_site_seq", job_site_seq);

                dbManager.AddParameters(5, "@prj_type_seq", prj_type_seq);
                dbManager.AddParameters(6, "@bgm_rec_manager", bgm_rec_manager);
                dbManager.AddParameters(7, "@bgm_rec_scope", bgm_rec_scope);
                dbManager.AddParameters(8, "@bgm_rec_code", bgm_rec_code);

                dbManager.AddParameters(9, "@bgm_rec_name", bgm_rec_name);
                dbManager.AddParameters(10, "@bgm_rec_descr", bgm_rec_descr);
                dbManager.AddParameters(11, "@bgm_rec_reason", bgm_rec_reason);
                dbManager.AddParameters(12, "@bgm_rec_object", bgm_rec_object);

                dbManager.AddParameters(13, "@bgm_rec_plan", bgm_rec_plan);
                dbManager.AddParameters(14, "@bgm_rec_target", bgm_rec_target);
                dbManager.AddParameters(15, "@bgm_rec_remark", bgm_rec_remark);
                dbManager.AddParameters(16, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(17, "@created_by", CreatedBy);
                dbManager.AddParameters(18, "@created_when", DateTime.Now);
                dbManager.AddParameters(19, "@job_site_seq_main", job_site_seq_main);
                string strsql = "Insert into bgm_receive ("
                        + "bgm_rec_header_seq,"
                        + "bgm_year,"
                        + "bgm_rec_date,"
                        + "bgm_strat_list_seq,"
                        + "job_site_seq,"
                        + "prj_type_seq,"
                        + "bgm_rec_manager,"
                        + "bgm_rec_scope,"
                        + "bgm_rec_code,"
                        + "bgm_rec_name,"
                        + "bgm_rec_descr,"
                        + "bgm_rec_reason,"
                        + "bgm_rec_object,"
                        + "bgm_rec_plan,"
                        + "bgm_rec_target,"
                        + "bgm_rec_remark,"
                        + "record_status,"
                        + "created_by,"
                        + "created_when,"
                        + "job_site_seq_main)"
                    + " values("
                        + "@bgm_rec_header_seq,"
                        + "@bgm_year,"
                        + "@bgm_rec_date,"
                        + "@bgm_strat_list_seq,"
                        + "@job_site_seq,"
                        + "@prj_type_seq,"
                        + "@bgm_rec_manager,"
                        + "@bgm_rec_scope,"
                        + "@bgm_rec_code,"
                        + "@bgm_rec_name,"
                        + "@bgm_rec_descr,"
                        + "@bgm_rec_reason,"
                        + "@bgm_rec_object,"
                        + "@bgm_rec_plan,"
                        + "@bgm_rec_target,"
                        + "@bgm_rec_remark,"
                        + "@record_status,"
                        + "@created_by,"
                        + "@created_when,"
                        + "@job_site_seq_main)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);


                string strsqlLast_insert = "select distinct scope_identity() FROM bgm_receive";
                DataSet ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strsqlLast_insert);
                dbManager.CommitTransaction();
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            catch
            {
                return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectCustoms(string bgm_rec_seq, string prj_ctrl_seq, string descr)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@bgm_rec_seq", bgm_rec_seq);
                dbManager.AddParameters(1, "@prj_ctrl_seq", prj_ctrl_seq);
                dbManager.AddParameters(2, "@descr", descr);
                string strsql = "Insert into bgm_receive_customs_field ("
                                            + "bgm_rec_seq,"
                                            + "prj_ctrl_seq,"
                                            + "descr)"
                                            + " values("
                                            + "@bgm_rec_seq,"
                                            + "@prj_ctrl_seq,"
                                            + "@descr)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectOption(string bgm_rec_seq, string descr, Nullable<Double> weight, string bgm_option_seq, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@bgm_rec_seq", bgm_rec_seq);
                dbManager.AddParameters(1, "@descr", descr); //bgm_rec_date
                dbManager.AddParameters(2, "@weight", weight);
                dbManager.AddParameters(3, "@bgm_option_seq", bgm_option_seq);

                dbManager.AddParameters(4, "@created_by", created_by);
                dbManager.AddParameters(5, "@created_when", DateTime.Now);
                string strsql = "Insert into bgm_receive_option ("
                                            + "bgm_rec_seq,"
                                            + "descr,"
                                            + "weight,"
                                            + "bgm_option_seq,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_rec_seq,"
                                            + "@descr,"
                                            + "@weight,"
                                            + "@bgm_option_seq,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }   
            catch
            {
               // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectKPI(string bgm_rec_kpi_name, string bgm_rec_kpi_descr, string unit, string bgm_rec_kpi_weight, string bgm_rec_seq, string bgm_kpi_type_seq, string score_1, string score_2, string score_3, string score_4, string score_5, string target_type
                , string target_m1, string target_m2, string target_m3, string target_m4, string target_m5, string target_m6, string target_m7, string target_m8, string target_m9, string target_m10, string target_m11, string target_m12
                , string mtef_1, string mtef_2, string mtef_3, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(29);
                dbManager.AddParameters(0, "@bgm_rec_kpi_name", bgm_rec_kpi_name);
                dbManager.AddParameters(1, "@bgm_rec_kpi_descr", bgm_rec_kpi_descr);
                dbManager.AddParameters(2, "@unit", unit);
                dbManager.AddParameters(3, "@bgm_rec_kpi_weight", bgm_rec_kpi_weight);
                dbManager.AddParameters(4, "@bgm_rec_seq", bgm_rec_seq);

                dbManager.AddParameters(5, "@bgm_kpi_type_seq", bgm_kpi_type_seq);
                dbManager.AddParameters(6, "@score_1", score_1);
                dbManager.AddParameters(7, "@score_2", score_2);
                dbManager.AddParameters(8, "@score_3", score_3);
                dbManager.AddParameters(9, "@score_4", score_4);
                dbManager.AddParameters(10, "@score_5", score_5);

                dbManager.AddParameters(11, "@target_type", target_type);
                dbManager.AddParameters(12, "@target_m1", target_m1);
                dbManager.AddParameters(13, "@target_m2", target_m2);
                dbManager.AddParameters(14, "@target_m3", target_m3);
                dbManager.AddParameters(15, "@target_m4", target_m4);
                dbManager.AddParameters(16, "@target_m5", target_m5);
                dbManager.AddParameters(17, "@target_m6", target_m6);
                dbManager.AddParameters(18, "@target_m7", target_m7);
                dbManager.AddParameters(19, "@target_m8", target_m8);
                dbManager.AddParameters(20, "@target_m9", target_m9);
                dbManager.AddParameters(21, "@target_m10", target_m10);
                dbManager.AddParameters(22, "@target_m11", target_m11);
                dbManager.AddParameters(23, "@target_m12", target_m12);
                dbManager.AddParameters(24, "@mtef_1", mtef_1);
                dbManager.AddParameters(25, "@mtef_2", mtef_2);
                dbManager.AddParameters(26, "@mtef_3", mtef_3);
                dbManager.AddParameters(27, "@created_by", created_by);
                dbManager.AddParameters(28, "@created_when", DateTime.Now);
                string strsql = "Insert into bgm_receive_kpi ("
                                             + "bgm_rec_kpi_name,"
                                             + "bgm_rec_kpi_descr,"
                                             + "unit,"
                                             + "bgm_rec_kpi_weight,"
                                             + "bgm_rec_seq,"
                                             + "bgm_kpi_type_seq,"
                                             + "score_1,"
                                             + "score_2,"
                                             + "score_3,"
                                             + "score_4,"
                                             + "score_5,"
                                             + "target_type,"
                                             + "target_m1,"
                                             + "target_m2,"
                                             + "target_m3,"
                                             + "target_m4,"
                                             + "target_m5,"
                                             + "target_m6,"
                                             + "target_m7,"
                                             + "target_m8,"
                                             + "target_m9,"
                                             + "target_m10,"
                                             + "target_m11,"
                                             + "target_m12,"
                                             + "mtef_1,"
                                             + "mtef_2,"
                                             + "mtef_3,"
                                             + "created_by,"
                                             + "created_when)"
                                             + " values("
                                             + "@bgm_rec_kpi_name,"
                                             + "@bgm_rec_kpi_descr,"
                                             + "@unit,"
                                             + "@bgm_rec_kpi_weight,"
                                             + "@bgm_rec_seq,"
                                             + "@bgm_kpi_type_seq,"
                                             + "@score_1,"
                                             + "@score_2,"
                                             + "@score_3,"
                                             + "@score_4,"
                                             + "@score_5,"
                                             + "@target_type,"
                                             + "@target_m1,"
                                             + "@target_m2,"
                                             + "@target_m3,"
                                             + "@target_m4,"
                                             + "@target_m5,"
                                             + "@target_m6,"
                                             + "@target_m7,"
                                             + "@target_m8,"
                                             + "@target_m9,"
                                             + "@target_m10,"
                                             + "@target_m11,"
                                             + "@target_m12,"
                                             + "@mtef_1,"
                                             + "@mtef_2,"
                                             + "@mtef_3,"
                                             + "@created_by,"
                                             + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public int InsertProjectActivityPlan(string bgm_rec_seq, string descr, int act_month1, int act_month2, int act_month3, int act_month4, int act_month5, int act_month6, int act_month7, int act_month8, int act_month9, int act_month10, int act_month11, int act_month12, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@bgm_rec_seq", bgm_rec_seq);
                dbManager.AddParameters(1, "@descr", descr); //bgm_rec_date
                dbManager.AddParameters(2, "@act_month1", act_month1);
                dbManager.AddParameters(3, "@act_month2", act_month2);

                dbManager.AddParameters(4, "@act_month3", act_month3);
                dbManager.AddParameters(5, "@act_month4", act_month4);
                dbManager.AddParameters(6, "@act_month5", act_month5);
                dbManager.AddParameters(7, "@act_month6", act_month6);
                dbManager.AddParameters(8, "@act_month7", act_month7);
                dbManager.AddParameters(9, "@act_month8", act_month8);
                dbManager.AddParameters(10, "@act_month9", act_month9);
                dbManager.AddParameters(11, "@act_month10", act_month10);
                dbManager.AddParameters(12, "@act_month11", act_month11);
                dbManager.AddParameters(13, "@act_month12", act_month12);
                dbManager.AddParameters(14, "@created_by", created_by);
                dbManager.AddParameters(15, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_receive_activity_plan ("
                                            + "bgm_rec_seq,"
                                            + "descr,"
                                            + "act_month1,"
                                            + "act_month2,"
                                            + "act_month3,"
                                            + "act_month4,"
                                            + "act_month5,"
                                            + "act_month6,"
                                            + "act_month7,"
                                            + "act_month8,"
                                            + "act_month9,"
                                            + "act_month10,"
                                            + "act_month11,"
                                            + "act_month12,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_rec_seq,"
                                            + "@descr,"
                                            + "@act_month1,"
                                            + "@act_month2,"
                                            + "@act_month3,"
                                            + "@act_month4,"
                                            + "@act_month5,"
                                            + "@act_month6,"
                                            + "@act_month7,"
                                            + "@act_month8,"
                                            + "@act_month9,"
                                            + "@act_month10,"
                                            + "@act_month11,"
                                            + "@act_month12,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                string strsqlLast_insert = "select distinct scope_identity() FROM bgm_receive_activity_plan";
                DataSet ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strsqlLast_insert);
                dbManager.CommitTransaction();
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            catch
            {
                 return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public void InsertProjectActivityPlanTarget(string bgm_rec_act_plan_seq, string target, string start_month, string end_month, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@bgm_rec_act_plan_seq", bgm_rec_act_plan_seq);
                dbManager.AddParameters(1, "@target", target); //bgm_rec_date
                dbManager.AddParameters(2, "@start_month", start_month);
                dbManager.AddParameters(3, "@end_month", end_month);

                dbManager.AddParameters(4, "@created_by", created_by);
                dbManager.AddParameters(5, "@created_when", DateTime.Now);
                string strsql = "Insert into bgm_receive_activity_plan_target ("
                                            + "bgm_rec_act_plan_seq,"
                                            + "target,"
                                            + "start_month,"
                                            + "end_month,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_rec_act_plan_seq,"
                                            + "@target,"
                                            + "@start_month,"
                                            + "@end_month,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public void InsertProjectExpense(string bgm_rec_seq, string bgm_source_seq, string bgm_exp_seq, string total_amount, string descr, Nullable<int> icm_res_item_seq, Nullable<int> unit_qty, Nullable<decimal> unit_price, Nullable<decimal> mtef1, Nullable<decimal> mtef2, Nullable<decimal> mtef3, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);
                dbManager.AddParameters(0, "@bgm_rec_seq", bgm_rec_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(3, "@total_amount", total_amount);
                dbManager.AddParameters(4, "@descr", descr);

                dbManager.AddParameters(5, "@icm_res_item_seq", icm_res_item_seq);
                dbManager.AddParameters(6, "@unit_qty", unit_qty);
                dbManager.AddParameters(7, "@unit_price", unit_price);
                dbManager.AddParameters(8, "@mtef1", mtef1);
                dbManager.AddParameters(9, "@mtef2", mtef2);
                dbManager.AddParameters(10, "@mtef3", mtef3);
                dbManager.AddParameters(11, "@created_by", created_by);
                dbManager.AddParameters(12, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_receive_expense ("
                                            + "bgm_rec_seq,"
                                            + "bgm_source_seq,"
                                            + "bgm_exp_seq,"
                                            + "total_amount,"
                                            + "descr,"
                                            + "icm_res_item_seq,"
                                            + "unit_qty,"
                                            + "unit_price,"
                                            + "mtef1,"
                                            + "mtef2,"
                                            + "mtef3,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_rec_seq,"
                                            + "@bgm_source_seq,"
                                            + "@bgm_exp_seq,"
                                            + "@total_amount,"
                                            + "@descr,"
                                            + "@icm_res_item_seq,"
                                            + "@unit_qty,"
                                            + "@unit_price,"
                                            + "@mtef1,"
                                            + "@mtef2,"
                                            + "@mtef3,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                //return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public void InsertProjectExpensePlan(string bgm_rec_exp_seq, string amount_month1, string amount_month2, string amount_month3, string amount_month4, string amount_month5, string amount_month6, string amount_month7, string amount_month8, string amount_month9, string amount_month10, string amount_month11, string amount_month12, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);
                dbManager.AddParameters(0, "@bgm_rec_exp_seq", bgm_rec_exp_seq);
                dbManager.AddParameters(1, "@amount_month1", amount_month1 != "" ? amount_month1 : "0" );
                dbManager.AddParameters(2, "@amount_month2", amount_month2 != "" ? amount_month2 : "0");
                dbManager.AddParameters(3, "@amount_month3", amount_month3 != "" ? amount_month3 : "0");
                dbManager.AddParameters(4, "@amount_month4", amount_month4 != "" ? amount_month4 : "0");
                dbManager.AddParameters(5, "@amount_month5", amount_month5 != "" ? amount_month5 : "0");
                dbManager.AddParameters(6, "@amount_month6", amount_month6 != "" ? amount_month6 : "0");
                dbManager.AddParameters(7, "@amount_month7", amount_month7 != "" ? amount_month7 : "0");
                dbManager.AddParameters(8, "@amount_month8", amount_month8 != "" ? amount_month8 : "0");
                dbManager.AddParameters(9, "@amount_month9", amount_month9 != "" ? amount_month9 : "0");
                dbManager.AddParameters(10, "@amount_month10", amount_month10 != "" ? amount_month10 : "0");
                dbManager.AddParameters(11, "@amount_month11", amount_month11 != "" ? amount_month11 : "0");
                dbManager.AddParameters(12, "@amount_month12", amount_month12 != "" ? amount_month12 : "0");
                dbManager.AddParameters(13, "@created_by", created_by);
                dbManager.AddParameters(14, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_receive_expense_plan ("
                                            + "bgm_rec_exp_seq,"
                                            + "amount_month1,"
                                            + "amount_month2,"
                                            + "amount_month3,"
                                            + "amount_month4,"
                                            + "amount_month5,"
                                            + "amount_month6,"
                                            + "amount_month7,"
                                            + "amount_month8,"
                                            + "amount_month9,"
                                            + "amount_month10,"
                                            + "amount_month11,"
                                            + "amount_month12,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_rec_exp_seq,"
                                            + "@amount_month1,"
                                            + "@amount_month2,"
                                            + "@amount_month3,"
                                            + "@amount_month4,"
                                            + "@amount_month5,"
                                            + "@amount_month6,"
                                            + "@amount_month7,"
                                            + "@amount_month8,"
                                            + "@amount_month9,"
                                            + "@amount_month10,"
                                            + "@amount_month11,"
                                            + "@amount_month12,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                //return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }




        public void UpdateProject(string Sequence, string bgm_year, DateTime bgm_rec_date, string bgm_rec_name
            , string bgm_strat_list_seq, string job_site_seq, string prj_type_seq, string bgm_rec_scope
            , string bgm_rec_reason, string bgm_rec_object, string bgm_rec_plan, string bgm_rec_target
            , string bgm_rec_remark, string date_start, string date_end, string UpdatedBy, string job_site_seq_main)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@bgm_strat_list_seq", bgm_strat_list_seq);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);

                dbManager.AddParameters(3, "@prj_type_seq", prj_type_seq);
                dbManager.AddParameters(4, "@bgm_rec_scope", bgm_rec_scope);

                dbManager.AddParameters(5, "@bgm_rec_name", bgm_rec_name);
                dbManager.AddParameters(6, "@bgm_rec_reason", bgm_rec_reason);
                dbManager.AddParameters(7, "@bgm_rec_object", bgm_rec_object);

                dbManager.AddParameters(8, "@bgm_rec_plan", bgm_rec_plan);
                dbManager.AddParameters(9, "@bgm_rec_target", bgm_rec_target);
                dbManager.AddParameters(10, "@bgm_rec_remark", bgm_rec_remark);
                dbManager.AddParameters(11, "@updated_by", UpdatedBy);
                dbManager.AddParameters(12, "@updated_when", DateTime.Now);
                dbManager.AddParameters(13, "@job_site_seq_main", job_site_seq_main);
                string strsql = "UPDATE bgm_receive"
                    + " SET bgm_strat_list_seq=@bgm_strat_list_seq"
                    + ", job_site_seq=@job_site_seq"
                    + ", job_site_seq_main=@job_site_seq_main"
                    + ", prj_type_seq_main=@prj_type_seq_main"
                    + ", bgm_rec_manager=@bgm_rec_manager"
                    + ", bgm_rec_scope=@bgm_rec_scope"
                    + ", bgm_rec_code=@bgm_rec_code"
                    + ", bgm_rec_name=@bgm_rec_name"
                    + ", bgm_rec_descr=@bgm_rec_descr"
                    + ", bgm_rec_reason=@bgm_rec_reason"
                    + ", bgm_rec_object=@bgm_rec_object"
                    + ", bgm_rec_plan=@bgm_rec_plan"
                    + ", bgm_rec_target=@bgm_rec_target"
                    + ", bgm_rec_remark=@bgm_rec_remark"
                    + ", updated_by=@updated_by"
                    + ", updated_when=@updated_when"
                    + " WHERE bgm_rec_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectOption(string Sequence, string descr, Nullable<Double> weight, string bgm_option_seq, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@bgm_option_seq", bgm_option_seq);
                dbManager.AddParameters(2, "@descr", descr);
                dbManager.AddParameters(3, "@weight", weight);
                dbManager.AddParameters(4, "@updated_by", UpdateBy);
                dbManager.AddParameters(5, "@updated_when", DateTime.Now);


                string strsql = "UPDATE bgm_receive_option SET descr=@descr, bgm_option_seq=@bgm_option_seq, weight=@weight, updated_by=@updated_by, updated_when=@updated_when WHERE bgm_rec_opt_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public void UpdateProjectKPI(string Sequence, string bgm_rec_kpi_name, string bgm_rec_kpi_descr, string unit, string bgm_rec_kpi_weight, string bgm_kpi_type_seq, string score_1, string score_2, string score_3, string score_4, string score_5, string target_type
            , string target_m1, string target_m2, string target_m3, string target_m4, string target_m5, string target_m6, string target_m7, string target_m8, string target_m9, string target_m10, string target_m11, string target_m12
            , string mtef_1, string mtef_2, string mtef_3, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(29);
                dbManager.AddParameters(0, "@bgm_rec_kpi_name", bgm_rec_kpi_name);
                dbManager.AddParameters(1, "@bgm_rec_kpi_descr", bgm_rec_kpi_descr);
                dbManager.AddParameters(2, "@unit", unit);
                dbManager.AddParameters(3, "@bgm_rec_kpi_weight", bgm_rec_kpi_weight);

                dbManager.AddParameters(4, "@bgm_kpi_type_seq", bgm_kpi_type_seq);
                dbManager.AddParameters(5, "@score_1", score_1);
                dbManager.AddParameters(6, "@score_2", score_2);
                dbManager.AddParameters(7, "@score_3", score_3);
                dbManager.AddParameters(8, "@score_4", score_4);
                dbManager.AddParameters(9, "@score_5", score_5);

                dbManager.AddParameters(10, "@target_type", target_type);
                dbManager.AddParameters(11, "@target_m1", target_m1);
                dbManager.AddParameters(12, "@target_m2", target_m2);
                dbManager.AddParameters(13, "@target_m3", target_m3);
                dbManager.AddParameters(14, "@target_m4", target_m4);
                dbManager.AddParameters(15, "@target_m5", target_m5);
                dbManager.AddParameters(16, "@target_m6", target_m6);
                dbManager.AddParameters(17, "@target_m7", target_m7);
                dbManager.AddParameters(18, "@target_m8", target_m8);
                dbManager.AddParameters(19, "@target_m9", target_m9);
                dbManager.AddParameters(20, "@target_m10", target_m10);
                dbManager.AddParameters(21, "@target_m11", target_m11);
                dbManager.AddParameters(22, "@target_m12", target_m12);
                dbManager.AddParameters(23, "@mtef_1", mtef_1);
                dbManager.AddParameters(24, "@mtef_2", mtef_2);
                dbManager.AddParameters(25, "@mtef_3", mtef_3);
                dbManager.AddParameters(26, "@updated_by", UpdateBy);
                dbManager.AddParameters(27, "@updated_when", DateTime.Now);
                dbManager.AddParameters(28, "@sequence", Sequence);

                string strsql = "UPDATE bgm_receive_kpi SET "
                                +" bgm_rec_kpi_name=@bgm_rec_kpi_name,"
                                + " bgm_rec_kpi_descr=@bgm_rec_kpi_descr,"
                                + " unit=@unit,"
                                + " bgm_rec_kpi_weight=@bgm_rec_kpi_weight,"
                                + " bgm_kpi_type_seq=@bgm_kpi_type_seq,"
                                + " score_1=@score_1,"
                                + " score_2=@score_2,"
                                + " score_3=@score_3,"
                                + " score_4=@score_4,"
                                + " score_5=@score_5,"
                                + " target_type=@target_type,"
                                + " target_m1=@target_m1,"
                                + " target_m2=@target_m2,"
                                + " target_m3=@target_m3,"
                                + " target_m4=@target_m4,"
                                + " target_m5=@target_m5,"
                                + " target_m6=@target_m6,"
                                + " target_m7=@target_m7,"
                                + " target_m8=@target_m8,"
                                + " target_m9=@target_m9,"
                                + " target_m10=@target_m10,"
                                + " target_m11=@target_m11,"
                                + " target_m12=@target_m12,"
                                + " mtef_1=@mtef_1,"
                                + " mtef_2=@mtef_2,"
                                + " mtef_3=@mtef_3,"
                                + " updated_by=@updated_by,"
                                + " updated_when=@updated_when WHERE bgm_rec_kpi_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public void UpdateProjectActivityPlan(string Sequence, string descr, int act_month1, int act_month2, int act_month3, int act_month4, int act_month5, int act_month6, int act_month7, int act_month8, int act_month9, int act_month10, int act_month11, int act_month12, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@descr", descr); //bgm_rec_date
                dbManager.AddParameters(1, "@act_month1", act_month1);
                dbManager.AddParameters(2, "@act_month2", act_month2);

                dbManager.AddParameters(3, "@act_month3", act_month3);
                dbManager.AddParameters(4, "@act_month4", act_month4);
                dbManager.AddParameters(5, "@act_month5", act_month5);
                dbManager.AddParameters(6, "@act_month6", act_month6);
                dbManager.AddParameters(7, "@act_month7", act_month7);
                dbManager.AddParameters(8, "@act_month8", act_month8);
                dbManager.AddParameters(9, "@act_month9", act_month9);
                dbManager.AddParameters(10, "@act_month10", act_month10);
                dbManager.AddParameters(11, "@act_month11", act_month11);
                dbManager.AddParameters(12, "@act_month12", act_month12);
                dbManager.AddParameters(13, "@updated_by", UpdateBy);
                dbManager.AddParameters(14, "@updated_when", DateTime.Now);
                dbManager.AddParameters(15, "@sequence", Sequence);


                string strsql = "UPDATE bgm_receive_activity_plan SET "
                                + " descr=@descr,"
                                + " act_month1=@act_month1,"
                                + " act_month2=@act_month2,"
                                + " act_month3=@act_month3,"
                                + " act_month4=@act_month4,"
                                + " act_month5=@act_month5,"
                                + " act_month6=@act_month6,"
                                + " act_month7=@act_month7,"
                                + " act_month8=@act_month8,"
                                + " act_month9=@act_month9,"
                                + " act_month10=@act_month10,"
                                + " act_month11=@act_month11,"
                                + " act_month12=@act_month12,"
                                + " updated_by=@updated_by,"
                                + " updated_when=@updated_when WHERE bgm_rec_act_plan_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public void UpdateProjectActivityPlanTarget(string Sequence, string target, string start_month, string end_month, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@target", target);
                dbManager.AddParameters(2, "@start_month", start_month);
                dbManager.AddParameters(3, "@end_month", end_month);
                dbManager.AddParameters(4, "@updated_by", UpdateBy);
                dbManager.AddParameters(5, "@updated_when", DateTime.Now);


                string strsql = "UPDATE bgm_receive_activity_plan_target SET target=@target, start_month=@start_month, end_month=@end_month, updated_by=@updated_by, updated_when=@updated_when WHERE bgm_rec_act_plan_target_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectExpense(string Sequence, string bgm_app_seq, string bgm_source_seq, string bgm_exp_seq, string total_amount, string descr, Nullable<int> icm_res_item_seq, Nullable<int> unit_qty, Nullable<decimal> unit_price, Nullable<decimal> mtef1, Nullable<decimal> mtef2, Nullable<decimal> mtef3, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(3, "@total_amount", total_amount);
                dbManager.AddParameters(4, "@descr", descr);
                dbManager.AddParameters(5, "@icm_res_item_seq", icm_res_item_seq);
                dbManager.AddParameters(6, "@unit_qty", unit_qty);
                dbManager.AddParameters(7, "@unit_price", unit_price);
                dbManager.AddParameters(8, "@mtef1", mtef1);
                dbManager.AddParameters(9, "@mtef2", mtef2);
                dbManager.AddParameters(10, "@mtef3", mtef3);
                dbManager.AddParameters(11, "@updated_by", UpdateBy);
                dbManager.AddParameters(12, "@updated_when", DateTime.Now);


                string strsql = "UPDATE bgm_receive_expense SET bgm_source_seq=@bgm_source_seq, bgm_exp_seq=@bgm_exp_seq, total_amount=@total_amount, descr=@descr, icm_res_item_seq=@icm_res_item_seq, unit_qty=@unit_qty, unit_price=@unit_price, mtef1=@mtef1, mtef2=@mtef2, mtef3=@mtef3, updated_by=@updated_by, updated_when=@updated_when WHERE bgm_rec_exp_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateTotalAmountProjectExpense(string Sequence, string Name, string used_amount
            , string cal_amount, string rpt_type, string CreateBy)
        {
            //try
            //{
            //    dbManager.Open();
            //    dbManager.BeginTransaction();
            //    dbManager.CreateParameters(4);
            //    dbManager.AddParameters(0, "@sequence", Sequence);
            //    dbManager.AddParameters(1, "@total_amount", total_amount);
            //    dbManager.AddParameters(2, "@updated_by", UpdateBy);
            //    dbManager.AddParameters(3, "@updated_when", DateTime.Now);

            //    strSQL = "UPDATE BGM_PROJECT_EXPENSE"
            //        + " SET total_amount=@total_amount"
            //            + ", updated_by=@updated_by"
            //            + ", updated_when=@updated_when"
            //            + " WHERE bgm_proj_exp_seq = @sequence";

            //    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            //    dbManager.CommitTransaction();
            //}
            //catch
            //{
            //    dbManager.RollBackTransaction();
            //}
            //finally
            //{
            //    dbManager.Dispose();
            //}
        }

        public void UpdateProjectExpensePlan(string Sequence, string amount_month1, string amount_month2, string amount_month3, string amount_month4, string amount_month5, string amount_month6, string amount_month7, string amount_month8, string amount_month9, string amount_month10, string amount_month11, string amount_month12, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@amount_month1", amount_month1);
                dbManager.AddParameters(1, "@amount_month2", amount_month2);
                dbManager.AddParameters(2, "@amount_month3", amount_month3);
                dbManager.AddParameters(3, "@amount_month4", amount_month4);
                dbManager.AddParameters(4, "@amount_month5", amount_month5);
                dbManager.AddParameters(5, "@amount_month6", amount_month6);
                dbManager.AddParameters(6, "@amount_month7", amount_month7);
                dbManager.AddParameters(7, "@amount_month8", amount_month8);
                dbManager.AddParameters(8, "@amount_month9", amount_month9);
                dbManager.AddParameters(9, "@amount_month10", amount_month10);
                dbManager.AddParameters(10, "@amount_month11", amount_month11);
                dbManager.AddParameters(11, "@amount_month12", amount_month12);
                dbManager.AddParameters(12, "@updated_by", UpdateBy);
                dbManager.AddParameters(13, "@updated_when", DateTime.Now);
                dbManager.AddParameters(14, "@sequence", Sequence);


                string strsql = "UPDATE bgm_receive_expense_plan SET "
                                + " amount_month1=@amount_month1,"
                                + " amount_month2=@amount_month2,"
                                + " amount_month3=@amount_month3,"
                                + " amount_month4=@amount_month4,"
                                + " amount_month5=@amount_month5,"
                                + " amount_month6=@amount_month6,"
                                + " amount_month7=@amount_month7,"
                                + " amount_month8=@amount_month8,"
                                + " amount_month9=@amount_month9,"
                                + " amount_month10=@amount_month10,"
                                + " amount_month11=@amount_month11,"
                                + " amount_month12=@amount_month12,"
                                + " updated_by=@updated_by,"
                                + " updated_when=@updated_when WHERE bgm_rec_exp_plan_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public void UpdateProjectCustoms(string Sequence, string descr)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@descr", descr);


                string strsql = "UPDATE bgm_receive_customs_field SET descr=@descr WHERE bgm_rec_cus_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }




        public void DeleteProject(string Seq, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@Seq", Seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);
                string strsql = "UPDATE bgm_receive"
                    + " SET record_status=@record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_rec_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectOption(string Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_receive_option WHERE bgm_rec_opt_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectKPI(string Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_receive_kpi WHERE bgm_rec_kpi_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectActivityPlan(string Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_receive_activity_plan WHERE bgm_rec_act_plan_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectActivityPlanTarget(string Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_receive_activity_plan_target WHERE bgm_rec_act_plan_target_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectExpense(string Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_receive_expense WHERE bgm_rec_exp_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void InsertProjectManager(string bgm_rec_seq, string emp_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@bgm_rec_seq", bgm_rec_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                string strsql = "Insert into bgm_receive_manager ("
                                            + "bgm_rec_seq,"
                                            + "emp_seq)"
                                            + " values("
                                            + "@bgm_rec_seq,"
                                            + "@emp_seq)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void DeleteProjectManager(string bgm_rec_seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", bgm_rec_seq);
                string strsql = "DELETE FROM bgm_receive_manager WHERE bgm_rec_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ApproveProject(string bgm_rec_seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", bgm_rec_seq);
                string strsql = "UPDATE bgm_receive set record_status = 'A' WHERE bgm_rec_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void ApproveProject(string bgm_req_seq, string status)
        {
            try
            {
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectManager(string bgm_rec_seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_rec_seq", bgm_rec_seq);

                string sql = "select r.bgm_rec_mng_seq as bgm_proj_mng_seq"
                            + ", concat(np.prefix_name, ' ' ,e.first_name, ' ', e.last_name) as name"
                            + " from bgm_receive_manager r"
                                + " inner join hrm_ps_employees e on r.emp_seq = e.emp_seq"
                                + " inner join mas_name_prefixes np on np.name_prefix_seq = e.name_prefix_seq"
                            + " where r.bgm_rec_seq = @bgm_rec_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public bool chkDubProjectOption(string bgm_rec_seq, string bgm_option_seq)
        {
            try
            {
                string sql = "select *"
                           + " from bgm_receive_option"
                           + " where bgm_rec_seq =" + bgm_rec_seq + " and bgm_option_seq =" + bgm_option_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                if (dt.Rows.Count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable GetDataProjectControlByJobSite(string job_site_seq, string HeaderID)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(2, "@HeaderID", HeaderID);

                string sql = "select r.bgm_rec_ctrl_seq as Sequence"
                           + ", s.bgm_strat_list_name as Strategy "
                           + ", j.job_site_name"
                           + ", (select coalesce(sum(total_amount), 0) from bgm_approval_control sc inner join bgm_approval_control_expense se on se.bgm_app_ctrl_seq = sc.bgm_app_ctrl_seq where sc.bgm_strat_list_seq = r.bgm_strat_list_seq and sc.job_site_seq = r.job_site_seq) as pre_total_amount"
                           + ", (select coalesce(sum(total_amount), 0) from bgm_receive_control_expense e where e.bgm_rec_ctrl_seq = r.bgm_rec_ctrl_seq) as total_amount"
                           + " from bgm_receive_control r"
                           + " left join bgm_receive_header h on r.bgm_rec_header_seq = h.bgm_rec_header_seq"
                           + " left join bgm_strategy_data_list s on r.bgm_strat_list_seq = s.bgm_strat_list_seq"
                           + " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
                           + " where r.record_status not like @RecordStatus"
                           + ((HeaderID.Length > 0) ? " and r.bgm_rec_header_seq = @HeaderID" : "");

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectByYearAndJobSite(string year, string job_site_seq, string emp_seq, string status)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);   // prj
                dbManager.AddParameters(1, "@Year", year);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);

                string strSQL = "select r.bgm_rec_seq as Sequence, r.bgm_rec_code as bgm_prj_code"
                    + ", r.bgm_rec_name as bgm_prj_name"
                    + ", r.date_start, r.date_end"
                    + ", re.total_amount"
                    + ", r.record_status, rs.record_status_name"
                    + " from bgm_receive r"
                    + " left join (select bgm_rec_seq"
                        + ", sum(coalesce(total_amount, 0)) as total_amount"
                        + " from bgm_receive_expense"
                        + " group by bgm_rec_seq"
                        + ") re on r.bgm_rec_seq = re.bgm_rec_seq"
                    + " left join sys_record_status rs on r.record_status = rs.record_status"
                    + " where r.record_status not like @RecordStatus"

                    + (job_site_seq.Length > 0 ? " and r.job_site_seq = @job_site_seq" : "")
                    + (year.Length > 0 ? " and r.bgm_year = @Year" : "");

                //string sql = "select r.bgm_req_seq as Sequence, r.bgm_req_code as bgm_prj_code"
                //                + ", cast(CONCAT( '[', "
                //                + " CASE r.record_status "
                //                + "      WHEN 'W' THEN 'ยังไม่ส่ง'"
                //                + "      WHEN 'P' THEN 'รออนุมัติ'"
                //                + "      WHEN 'A' THEN 'อนุมัติแล้ว'"
                //                + "      WHEN 'R' THEN 'ตีกลับ'"
                //                + " END "
                //                + ", '] - ', cast( r.bgm_req_name as char)) as char) as bgm_prj_name"

                //                + ", s.bgm_strat_list_name as Strategy"
                //                + ", s.bgm_strat_list_name , j.job_site_name"
                //            + " from bgm_request r"
                //                + " left join bgm_strategy_data_list s on r.bgm_strat_list_seq = s.bgm_strat_list_seq"
                //                + " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
                //            + " where r.bgm_year = @Year and r.job_site_seq = @job_site_seq and r.record_status not like @RecordStatus"
                //            + " order by bgm_req_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectByYearAndJobSiteAndSubAct(string bgm_year, string job_site_seq, string sub_act, string status)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectControlBySequenceForGridIn(string seq, string job_site_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetReport_01(string year)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ApproveRecordStatus);


                string strsql = "select upper_bgm_strat_list_seq, upper_bgm_strat_list_name"
                        + ", bgm_strat_list_seq, bgm_strat_list_name, job_site_name_short as remark, duration"
                        + ", bgm_rec_seq as bgm_proj_seq, bgm_rec_code as bgm_proj_code, bgm_rec_name as bgm_proj_name"
                        + ", coalesce(sum(TotalAmountSource1), 0) as TotalAmountSource1, coalesce(sum(TotalAmountSource6), 0) as TotalAmountSource6"
                        + ", coalesce(sum(TotalAmountSource7), 0) as TotalAmountSource7, coalesce(sum(TotalAmountSourceEtc), 0) as TotalAmountSourceEtc"
                        + " from ("
                            + "select ds2.bgm_strat_list_seq as upper_bgm_strat_list_seq, ds2.bgm_strat_list_name as upper_bgm_strat_list_name"
                            + ", ds1.bgm_strat_list_seq, ds1.bgm_strat_list_name, js.job_site_name_short"
                            + ", r.bgm_rec_seq, r.bgm_rec_code, r.bgm_rec_name"
                            + ", '' as duration"
                            + ", case when e.bgm_source_seq = 1 then coalesce(sum(e.total_amount), 0) end as TotalAmountSource1"
                            + ", case when e.bgm_source_seq = 6 then coalesce(sum(e.total_amount), 0) end as TotalAmountSource6"
                            + ", case when e.bgm_source_seq = 7 then coalesce(sum(e.total_amount), 0) end as TotalAmountSource7"
                            + ", case when e.bgm_source_seq not like 1 and e.bgm_source_seq not like 6 and e.bgm_source_seq not like 7 then coalesce(sum(e.total_amount), 0) end as TotalAmountSourceEtc"
                        + " from  bgm_receive r"
                            + " inner join bgm_receive_expense e on e.bgm_rec_seq = r.bgm_rec_seq"
                            + " inner join bgm_mas_source s on s.bgm_source_seq = e.bgm_source_seq"
                            + " inner join bgm_strategy_data_mapping mp1 on mp1.bgm_strat_list_seq = r.bgm_strat_list_seq"
                            + " inner join bgm_strategy_data_list ds1 on ds1.bgm_strat_list_seq = mp1.bgm_strat_list_seq"
                            + " inner join bgm_strategy_data_mapping mp2 on mp2.bgm_strat_data_map_seq = mp1.upper_bgm_strat_data_map_seq"
                            + " inner join bgm_strategy_data_list ds2 on ds2.bgm_strat_list_seq = mp2.bgm_strat_list_seq"
                            + " inner join hrm_mp_job_sites js on js.job_site_seq = r.job_site_seq"
                        + " where r.bgm_year = @year"
                            + " and r.record_status = @RecordStatus"
                        + " group by ds2.bgm_strat_list_seq, ds2.bgm_strat_list_name, ds1.bgm_strat_list_seq, ds1.bgm_strat_list_name, js.job_site_name_short, s.bgm_source_seq, r.bgm_rec_seq, r.bgm_rec_code, r.bgm_rec_name"
                    + " ) t1"
                    + " group by upper_bgm_strat_list_seq, upper_bgm_strat_list_name, bgm_strat_list_seq, bgm_strat_list_name, job_site_name_short, bgm_rec_seq, bgm_rec_code, bgm_rec_name";

                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetReport_01_01(string year)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ApproveRecordStatus);

                string strsql = "select r.bgm_req_seq as bgm_proj_seq, t.bgm_kpi_type_seq, t.bgm_kpi_type_name"
                        + ", k.bgm_req_kpi_seq as bgm_proj_kpi_seq, k.bgm_req_kpi_name as bgm_proj_kpi_name, k.unit"
                        + ", coalesce(target_m1, 0) + coalesce(target_m2, 0) + coalesce(target_m3, 0) + coalesce(target_m4, 0)"
                        + "+ coalesce(target_m5, 0) + coalesce(target_m6, 0) + coalesce(target_m7, 0) + coalesce(target_m8, 0)"
                        + "+ coalesce(target_m9, 0) + coalesce(target_m10, 0) + coalesce(target_m11, 0) + coalesce(target_m12, 0) as target"
                        + ", k.bgm_req_kpi_weight as weight"
                        + " from bgm_request r"
                            + " inner join bgm_request_kpi k on r.bgm_req_seq = k.bgm_req_seq"
                            + " inner join bgm_mas_kpi_type t on k.bgm_kpi_type_seq = t.bgm_kpi_type_seq"
                        + " where r.bgm_year = @year";

                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetReport_01_02(string year)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ApproveRecordStatus);

                string strsql = "select r.bgm_req_seq as bgm_proj_seq, mo2.bgm_option_seq as upper_bgm_option_seq, mo2.bgm_option_name as upper_bgm_option_name"
                        + ", mo1.bgm_option_seq, mo1.bgm_option_name"
                        + " from bgm_request r"
                            + " inner join bgm_request_option o on r.bgm_req_seq = o.bgm_req_seq"
                            + " inner join bgm_mas_option mo1 on mo1.bgm_option_seq = o.bgm_option_seq"
                            + " inner join bgm_mas_option mo2 on mo2.bgm_option_seq = mo1.upper_bgm_option_seq"
                        + " where r.bgm_year = @year"
                            + " and mo1.bgm_option_type_seq = 2";

                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetReport_04(string year)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ApproveRecordStatus);


                string strsql = "select upper_bgm_strat_list_seq, upper_bgm_strat_list_name"
                        + ", bgm_strat_list_seq, bgm_strat_list_name, ProjectCount"
                        + ", coalesce(sum(TotalAmountSource1), 0) as TotalAmountSource1, coalesce(sum(TotalAmountSource6), 0) as TotalAmountSource6"
                        + ", coalesce(sum(TotalAmountSource7), 0) as TotalAmountSource7, coalesce(sum(TotalAmountSourceEtc), 0) as TotalAmountSourceEtc"
                        + " from ("
                            + "select ds2.bgm_strat_list_seq as upper_bgm_strat_list_seq, ds2.bgm_strat_list_name as upper_bgm_strat_list_name"
                            + ", ds1.bgm_strat_list_seq, ds1.bgm_strat_list_name, (select count(*) from bgm_receive s where s.bgm_strat_list_seq = r.bgm_strat_list_seq and s.record_status = 'A' ) as ProjectCount"
                            + ", s.bgm_source_seq, s.bgm_source_name"
                            + ", case when s.bgm_source_seq = 1 then coalesce(sum(e.total_amount), 0) end as TotalAmountSource1"
                            + ", case when s.bgm_source_seq = 6 then coalesce(sum(e.total_amount), 0) end as TotalAmountSource6"
                            + ", case when s.bgm_source_seq = 7 then coalesce(sum(e.total_amount), 0) end as TotalAmountSource7"
                            + ", case when s.bgm_source_seq not like 1 and s.bgm_source_seq not like 6 and s.bgm_source_seq not like 7 then coalesce(sum(e.total_amount), 0) end as TotalAmountSourceEtc"
                        + " from  bgm_receive r"
                            + " inner join bgm_receive_expense e on e.bgm_rec_seq = r.bgm_rec_seq"
                            + " inner join bgm_mas_source s on s.bgm_source_seq = e.bgm_source_seq"
                            + " inner join bgm_strategy_data_mapping mp1 on mp1.bgm_strat_list_seq = r.bgm_strat_list_seq"
                            + " inner join bgm_strategy_data_list ds1 on ds1.bgm_strat_list_seq = mp1.bgm_strat_list_seq"
                            + " inner join bgm_strategy_data_mapping mp2 on mp2.bgm_strat_data_map_seq = mp1.upper_bgm_strat_data_map_seq"
                            + " inner join bgm_strategy_data_list ds2 on ds2.bgm_strat_list_seq = mp2.bgm_strat_list_seq"
                        + " where r.bgm_year = @year"
                            + " and r.record_status = @RecordStatus"
                        + " group by ds2.bgm_strat_list_seq, ds2.bgm_strat_list_name, ds1.bgm_strat_list_seq, ds1.bgm_strat_list_name, s.bgm_source_seq, s.bgm_source_name"
                    + " ) t1"
                    + " group by upper_bgm_strat_list_seq, upper_bgm_strat_list_name, bgm_strat_list_seq, bgm_strat_list_name, ProjectCount";

                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetReport_05(string year, string type)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@type", type);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._ApproveRecordStatus);


                string strsql = "select upper_bgm_option_seq, upper_bgm_option_name"
                        + ", bgm_option_seq, bgm_option_name, ProjectCount"
                        + ", coalesce(sum(TotalAmountSource1), 0) as TotalAmountSource1, coalesce(sum(TotalAmountSource6), 0) as TotalAmountSource6"
                        + ", coalesce(sum(TotalAmountSource7), 0) as TotalAmountSource7, coalesce(sum(TotalAmountSourceEtc), 0) as TotalAmountSourceEtc"
                        + " from("
                            + "select  parent_seq as upper_bgm_option_seq, parent_name as upper_bgm_option_name, bgm_option_seq, bgm_option_name"
                            + ", count(bgm_rec_seq) as ProjectCount, bgm_source_seq, bgm_source_name"
                            + ", case when bgm_source_seq = 1 then coalesce(sum(total_amount), 0) end as TotalAmountSource1"
                            + ", case when bgm_source_seq = 6 then coalesce(sum(total_amount), 0) end as TotalAmountSource6"
                            + ", case when bgm_source_seq = 7 then coalesce(sum(total_amount), 0) end as TotalAmountSource7"
                            + ", case when bgm_source_seq not like 1 and bgm_source_seq not like 6 and bgm_source_seq not like 7 then coalesce(sum(total_amount), 0) end as TotalAmountSourceEtc"
                        + " from ("
                            + " select mo.bgm_option_seq as parent_seq, mo.bgm_option_name as parent_name, o1.bgm_option_seq, o1.bgm_option_name, r.bgm_rec_seq, r.bgm_rec_code"
                                + ", bgm_rec_name, s.bgm_source_seq, s.bgm_source_name, (o1.weight * coalesce(sum(e.total_amount), 0)) / 100 as total_amount"
                            + " from bgm_mas_option mo"
                            + " left join (	select o1.bgm_rec_seq, o1.bgm_option_seq, mo.upper_bgm_option_seq, o1.weight, mo.bgm_option_name"
                                        + " from bgm_receive_option o1 "
                                            + " left join bgm_mas_option mo on o1.bgm_option_seq = mo.bgm_option_seq "
                                        + " where mo.bgm_option_type_seq = @type"
                                        + " ) o1 on mo.bgm_option_seq = o1.upper_bgm_option_seq"
                            + " inner join bgm_receive r  on o1.bgm_rec_seq = r.bgm_rec_seq"
                            + " inner join bgm_receive_expense e on e.bgm_rec_seq = r.bgm_rec_seq"
                            + " inner join bgm_mas_source s on s.bgm_source_seq = e.bgm_source_seq"
                        + " where r.bgm_year = @year"
                            + " and  r.record_status = @RecordStatus"
                        + " group by mo.bgm_option_seq, mo.bgm_option_name, o1.bgm_option_seq, o1.bgm_option_name, r.bgm_rec_seq, r.bgm_rec_code, bgm_rec_name, s.bgm_source_seq, s.bgm_source_name"
                        + " ) t"
                        + " group by  parent_seq, parent_name, bgm_option_seq, bgm_option_name, bgm_source_seq, bgm_source_name"
                    + " ) t1"
                    + " group by upper_bgm_option_seq, upper_bgm_option_name, bgm_option_seq, bgm_option_name, ProjectCount";

                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetReport_06(string year, string typeParent, string type)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@typeParent", typeParent);
                dbManager.AddParameters(2, "@type", type);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ApproveRecordStatus);

                string strsql = "select o2.bgm_option_seq as upper_bgm_option_seq, o2.bgm_option_name as upper_bgm_option_name, parent_seq as bgm_option_seq , parent_name as bgm_option_name"
                                + ", r.bgm_rec_seq as bgm_proj_seq, r.bgm_rec_code as bgm_proj_code, r.bgm_rec_name as bgm_proj_name"
                                + ", (sum(ro.weight) * TotalAmountSource1) / 100 as TotalAmountSource1, (sum(ro.weight) * TotalAmountSource6) / 100 as TotalAmountSource6, (sum(ro.weight) * TotalAmountSource7) / 100 as TotalAmountSource7"
                            + " from ("
                                    + " select parent_seq, parent_name, bgm_rec_seq, bgm_rec_code, bgm_rec_name"
                                        + ", coalesce(sum(TotalAmountSource1), 0) as TotalAmountSource1, coalesce(sum(TotalAmountSource6), 0) as TotalAmountSource6, coalesce(sum(TotalAmountSource7), 0) as TotalAmountSource7"
                                    + " from ("
                                                + " select mo.bgm_option_seq as parent_seq, mo.bgm_option_name as parent_name, r.bgm_rec_seq, r.bgm_rec_code, bgm_rec_name"
                                                    + ", case when s.bgm_source_seq = 1 then (o1.weight * coalesce(sum(e.total_amount), 0)) / 100 end as TotalAmountSource1"
                                                    + ", case when s.bgm_source_seq = 6 then (o1.weight * coalesce(sum(e.total_amount), 0)) / 100 end as TotalAmountSource6"
                                                    + ", case when s.bgm_source_seq = 7 then (o1.weight * coalesce(sum(e.total_amount), 0)) / 100 end as TotalAmountSource7"
                                                + " from bgm_mas_option mo"
                                                    + " left join (	select o1.bgm_rec_seq, o1.bgm_option_seq, mo.upper_bgm_option_seq, o1.weight, mo.bgm_option_name"
                                                                + " from bgm_receive_option o1 "
                                                                    + " left join bgm_mas_option mo on o1.bgm_option_seq = mo.bgm_option_seq"
                                                                + " where mo.bgm_option_type_seq = @type"
                                                            + " ) o1 on mo.bgm_option_seq = o1.upper_bgm_option_seq"
                                                    + " inner join bgm_receive r  on o1.bgm_rec_seq = r.bgm_rec_seq"
                                                    + " inner join bgm_receive_expense e on e.bgm_rec_seq = r.bgm_rec_seq"
                                                    + " inner join bgm_mas_source s on s.bgm_source_seq = e.bgm_source_seq"
                                                + " where r.bgm_year = @year"
                                                    + " and r.record_status = @RecordStatus"
                                                + " group by mo.bgm_option_seq, mo.bgm_option_name, r.bgm_rec_seq, r.bgm_rec_code, bgm_rec_name, s.bgm_source_seq, s.bgm_source_name"
                                            + " ) t"
                                    + " group by  parent_seq, parent_name, bgm_rec_seq, bgm_rec_code, bgm_rec_name"
                                    + ") r"
                                + " inner join bgm_receive_option ro  on r.bgm_rec_seq = ro.bgm_rec_seq"
                                + " inner join bgm_mas_option o1 on ro.bgm_option_seq = o1.bgm_option_seq "
                                + " inner join bgm_mas_option o2 on o1.upper_bgm_option_seq = o2.bgm_option_seq"
                            + " where o1.bgm_option_type_seq = @typeParent"
                            + " group by o2.bgm_option_seq, o2.bgm_option_name, r.bgm_rec_seq, r.bgm_rec_code, r.bgm_rec_name, parent_seq, parent_name";

                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetReport_02(string year)
        {
            //try
            //{
            //    dbManager.CreateParameters(2);
            //    dbManager.AddParameters(0, "@year", year);
            //    dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ApproveRecordStatus);

            //    string strsql = "select j.job_site_seq, j.job_site_ref, j.job_site_name"
            //        + ", sl.bgm_strat_list_seq, sl.bgm_strat_list_code, sl.bgm_strat_list_name"
            //        + ", s.bgm_source_seq, s.bgm_source_name"
            //        + ", e2.bgm_exp_seq, e2.bgm_exp_code, e2.bgm_exp_name"
            //        + ", sum(coalesce(re.total_amount, 0)) as total_amount"

            //        + " from bgm_request r"
            //        + " inner join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq"
            //        + " inner join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
            //        + " inner join bgm_strategy_data_list sl on r.bgm_strat_list_seq = sl.bgm_strat_list_seq"
            //        + " inner join bgm_mas_source s on re.bgm_source_seq = s.bgm_source_seq"
            //        + " inner join bgm_mas_expense e1 on re.bgm_exp_seq = e1.bgm_exp_seq"
            //        + " left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq"

            //        + " where r.record_status = @RecordStatus"
            //        + " and r.bgm_year = @year"

            //        + " group by j.job_site_seq, j.job_site_ref, j.job_site_name"
            //        + ", sl.bgm_strat_list_seq, sl.bgm_strat_list_code, sl.bgm_strat_list_name"
            //        + ", s.bgm_source_seq, s.bgm_source_name"
            //        + ", e2.bgm_exp_seq, e2.bgm_exp_code, e2.bgm_exp_name";

            //    dbManager.Open();
            //    DataTable dt = new DataTable();
            //    dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

            //    return dt;
            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
            //finally
            //{
            //    dbManager.Dispose();
            //}

            return null;
        }


        public void DeleteProjectOptionFromProjSeq(string BgmProjSeq, string BgmOptionTypeSeq)
        {
            throw new NotImplementedException();
        }

        public DataTable ListProjectOption(string bgm_proj_seq, string optionType)
        {
            throw new NotImplementedException();
        }
    }
}
